import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.utils import formataddr
from datetime import datetime, date, timedelta

import pymysql
import pandas as pd


def load_data():
    # 连接数据库
    conn = pymysql.Connect(
        host='bcyang.rwlb.rds.aliyuncs.com',
        port=3306,
        user='big_data',
        passwd='Bu@Data@0416',
        db='big-data-v1',
        charset='utf8'
    )

    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    cursor.execute(('''SELECT
     *
    FROM
     (
      SELECT
       a.g001,
       a.fname,
       count(1) AS num
      FROM
       bcw_pig_ai_warn a
      WHERE
       1 = 1
      AND a.correctdstatus = 2
      AND a.handledstatus = 1
      AND a.alarmTime > curdate()
      GROUP BY
       a.g001,
       a.fname
     ) t
    ORDER BY
     t.num DESC'''))
    daxiang_devices = cursor.fetchall()  # 只获取大象的数据
    data_list = []
    for device in daxiang_devices:
        data_list.append(device.values())

    return data_list


def all_data_mail(email_content, subject):
    all_data_receivers = ['83942052@qq.com', 'lijianyu741@163.com']  # 收件人邮箱账号
    # all_data_receivers = ['lijianyu741@163.com']  # 收件人邮箱账号
    for my_user in all_data_receivers:
        my_sender = '83942052@qq.com'  # 发件人邮箱账号
        my_pass = 'benzlgfffdzxbgde'  # 发件人邮箱密码

        dataframe = pd.DataFrame(email_content, columns=['g001', 'fname', 'num'])
        dataframe.to_excel('test.xlsx')
        # 创建一个带附件的实例
        msg = MIMEMultipart()
        msg['From'] = formataddr(["不愁网每日提醒", my_sender])  # 括号里的对应发件人邮箱昵称、发件人邮箱账号
        msg['To'] = formataddr(["FK", my_user])  # 括号里的对应收件人邮箱昵称、收件人邮箱账号
        msg['Subject'] = subject  # 邮件的主题，也可以说是标题

        # 构造附件1，传送当前目录下的 test.txt 文件
        att1 = MIMEText(open('test.xlsx', 'rb').read(), 'base64', 'utf-8')
        att1["Content-Type"] = 'application/octet-stream'
        # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
        att1["Content-Disposition"] = 'attachment; filename="' + str(date.today()) + '.xlsx"'
        msg.attach(att1)

        server = smtplib.SMTP_SSL("smtp.qq.com", 465)  # 发件人邮箱中的SMTP服务器，端口是25
        server.login(my_sender, my_pass)  # 括号中对应的是发件人邮箱账号、邮箱密码
        server.sendmail(my_sender, [my_user, ], msg.as_string())  # 括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
        server.quit()  # 关闭连接
        ret = True

    return ret


all_data_mail(load_data(), "山西大象每日数据" + (date.today()).strftime("%Y-%m-%d"))
